unit CustomerLoanSettingForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, uADStanIntf, uADStanOption,
  uADStanError, uADGUIxIntf, uADPhysIntf, uADStanDef, uADStanPool, uADStanAsync,
  uADPhysManager, uADGUIxFormsWait, uADStanParam, uADDatSManager, uADDAptIntf,
  uADDAptManager, Vcl.StdCtrls, Vcl.DBCtrls, uADCompClient, Data.DB, UDF,
  uADCompDataSet, uADPhysIB, uADPhysMySQL, uADCompGUIx, Vcl.ComCtrls, Vcl.Grids,
  Vcl.DBGrids, Vcl.ExtCtrls, System.UITypes;

type
  TfmCustomerLoanSetting = class(TForm)
    ADConnection1: TADConnection;
    ADGUIxWaitCursor1: TADGUIxWaitCursor;
    ADPhysMySQLDriverLink1: TADPhysMySQLDriverLink;
    ADPhysIBDriverLink1: TADPhysIBDriverLink;
    dsCustomers: TDataSource;
    adtCustomers: TADTable;
    qrySQL: TADQuery;
    Panel1: TPanel;
    Label2: TLabel;
    dblcbCustomerCode: TDBLookupComboBox;
    Label1: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    txtLoanAmount: TEdit;
    txtInterest: TEdit;
    Label11: TLabel;
    txtPeriod: TEdit;
    cbPeriodType: TComboBox;
    txtDaysOfPeriod: TEdit;
    UpDown1: TUpDown;
    txtPayingAmount: TEdit;
    txtInstallments: TEdit;
    txtPaymentPerInstallment: TEdit;
    txtLastPeriodPayment: TEdit;
    Label12: TLabel;
    dsLoanSettings: TDataSource;
    adtLoanSettings: TADTable;
    DBGrid1: TDBGrid;
    Label13: TLabel;
    DBGrid2: TDBGrid;
    Label14: TLabel;
    btnNew: TButton;
    btnEdit: TButton;
    btnDelete: TButton;
    btnSave: TButton;
    btnClose: TButton;
    procedure FormCreate(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure btnEditClick(Sender: TObject);
    procedure btnDeleteClick(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure DBGrid2CellClick(Column: TColumn);
    procedure txtLoanAmountExit(Sender: TObject);
    procedure txtInterestExit(Sender: TObject);
    procedure txtPayingAmountChange(Sender: TObject);
    procedure txtInstallmentsChange(Sender: TObject);
    procedure txtPeriodChange(Sender: TObject);
    procedure txtDaysOfPeriodChange(Sender: TObject);
    procedure cbPeriodTypeChange(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
  private
    { Private declarations }
    DBOp: TDBOp;
    PeriodTypeList: TStringList; //Name=Value pair for cbPeriodType
    ActiveLoan: boolean;
    CurrentRow: TBookmark;

    procedure ClearDetails;
    procedure SetDetails;
    procedure SaveDetails;
    procedure SetPayingAmount;
    procedure SetPaymentSchedule;
    procedure InitPeriodType;
    function HaveLoanActive: boolean;
  public
    { Public declarations }
  end;

var
  fmCustomerLoanSetting: TfmCustomerLoanSetting;

implementation

{$R *.dfm}

procedure TfmCustomerLoanSetting.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfmCustomerLoanSetting.btnDeleteClick(Sender: TObject);
var
  AffectedRows: integer;
begin
  if MessageDlg('Confirm delete?', mtConfirmation, [mbOk, mbCancel], 0) = mrCancel then Exit;
  try
    AffectedRows := ADConnection1.ExecSQL('Delete from customerloansettings Where ID=:ID', [adtLoanSettings.FieldByName('ID').AsInteger]);
    if AffectedRows <= 0 then
      ShowMessage('No such record found, no record be deleted.');
    if AffectedRows > 1 then
      ShowMessage('More than one record be deleted!');
    ADConnection1.Commit;
    adtCustomers.Refresh;
    adtLoanSettings.Refresh;
    ClearDetails;
  except
    on E : Exception do
    begin
      ShowMessage('Exception message = '+E.Message);
      ADConnection1.Rollback;
    end;
  end;
end;

procedure TfmCustomerLoanSetting.btnEditClick(Sender: TObject);
begin
  SetDetails;
end;

procedure TfmCustomerLoanSetting.btnNewClick(Sender: TObject);
begin
  ClearDetails;
end;

procedure TfmCustomerLoanSetting.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
begin
  CanClose := True;
  FreeAndNil(fmCustomerLoanSetting);
end;

procedure TfmCustomerLoanSetting.FormCreate(Sender: TObject);
var
  ADPhysDriverLink: TADPhysDriverLink;
  DriverID: string;
begin
  DriverID := DBDriverID;
  if DriverID = 'Firebird' then
    ADPhysDriverLink := ADPhysIBDriverLink1;
  if DriverID = 'MySQL' then
    ADPhysDriverLink := ADPhysMySQLDriverLink1;
  SetDBInfo(ADConnection1, ADPhysDriverLink);
  adtCustomers.Active := True;
  adtLoanSettings.Active := True;
  InitPeriodType;
end;

procedure TfmCustomerLoanSetting.btnSaveClick(Sender: TObject);
begin
  //check active loan setting
  if HaveLoanActive then
  begin
    ShowMessage('Have loan active for this customer, can create new one!');
    Exit;
  end;
  SaveDetails;
  ClearDetails;
end;

procedure TfmCustomerLoanSetting.cbPeriodTypeChange(Sender: TObject);
begin
  txtDaysOfPeriod.Text := PeriodTypeList.Values[cbPeriodType.Items[cbPeriodType.ItemIndex]];
end;

procedure TfmCustomerLoanSetting.ClearDetails;
begin
  dblcbCustomerCode.KeyValue := '';
  dblcbCustomerCode.Enabled := True;
  txtLoanAmount.Text := '0.00';
  txtInterest.Text := '0';
  txtPayingAmount.Text := '0.00';
  txtPeriod.Text := '30';
  cbPeriodType.ItemIndex := 0;
  txtDaysOfPeriod.Text := '1';
  txtInstallments.Text := '30';
  txtPaymentPerInstallment.Text := '0.00';
  txtLastPeriodPayment.Text := '0.00';
  ActiveLoan := False;
  DBOp := opInsert;
end;

procedure TfmCustomerLoanSetting.DBGrid1CellClick(Column: TColumn);
begin
  if CurrentRow <> DBGrid1.DataSource.DataSet.Bookmark then
  begin
    ClearDetails;
    CurrentRow := DBGrid1.DataSource.DataSet.Bookmark;
  end;
end;

procedure TfmCustomerLoanSetting.DBGrid2CellClick(Column: TColumn);
begin
  SetDetails;
end;

procedure TfmCustomerLoanSetting.SetDetails;
var
  TempStr: string;
begin
  with DBGrid2.DataSource.DataSet do
  begin
    dblcbCustomerCode.KeyValue := FieldByName('CUSTOMER_CODE').AsVariant;
    dblcbCustomerCode.Enabled := False;
    if FieldByName('LOAN_AMOUNT').IsNull then
      TempStr := '0.00'
    else
      TempStr := FieldByName('LOAN_AMOUNT').AsString;
    txtLoanAmount.Text := Format('%.2f', [StrToFloat(TempStr)]);

    if FieldByName('INTEREST').IsNull then
      TempStr := '0.00'
    else
      TempStr := FieldByName('INTEREST').AsString;
    txtInterest.Text := Format('%.2f', [StrToFloat(TempStr)]);

    if FieldByName('PAYING_AMOUNT').IsNull then
      TempStr := '0.00'
    else
      TempStr := FieldByName('PAYING_AMOUNT').AsString;
    txtPayingAmount.Text := Format('%.2f', [StrToFloat(TempStr)]);

    if FieldByName('PERIOD').IsNull then
      txtPeriod.Text := '0'
    else
      txtPeriod.Text := FieldByName('PERIOD').AsString;

    if FieldByName('PERIOD_TYPE').IsNull then
      cbPeriodType.ItemIndex := -1
    else
      cbPeriodType.ItemIndex :=cbPeriodType.Items.IndexOf(FieldByName('PERIOD_TYPE').AsString);

    if FieldByName('DAYS_OF_PERIOD').IsNull then
      txtDaysOfPeriod.Text := '0'
    else
      txtDaysOfPeriod.Text := FieldByName('DAYS_OF_PERIOD').AsString;

    if FieldByName('DAYS_OF_PERIOD').IsNull then
      txtInstallments.Text := '0'
    else
      txtInstallments.Text := FieldByName('INSTALLMENTS').AsString;

    if FieldByName('PAYMENT_PER_INSTALLMENT').IsNull then
      TempStr := '0.00'
    else
      TempStr := FieldByName('PAYMENT_PER_INSTALLMENT').AsString;
    txtPaymentPerInstallment.Text := Format('%.2f', [StrToFloat(TempStr)]);

    if FieldByName('LAST_PERIOD_PAYMENT').IsNull then
      TempStr := '0.00'
    else
      TempStr := FieldByName('LAST_PERIOD_PAYMENT').AsString;
    txtLastPeriodPayment.Text := Format('%.2f', [StrToFloat(TempStr)]);

    ActiveLoan := FieldByName('ACTIVE').AsBoolean;
    if not ActiveLoan then
      DBOp := opDenied
    else
      DBOp := opUpdate;
  end;
end;

procedure TfmCustomerLoanSetting.txtDaysOfPeriodChange(Sender: TObject);
begin
  txtInstallments.Text := Format('%d', [StrToInt(txtPeriod.Text) * StrToInt(txtDaysOfPeriod.Text)]);
end;

procedure TfmCustomerLoanSetting.txtInstallmentsChange(Sender: TObject);
begin
  SetPaymentSchedule;
end;

procedure TfmCustomerLoanSetting.txtInterestExit(Sender: TObject);
begin
  SetPayingAmount;
  SetPaymentSchedule;
end;

procedure TfmCustomerLoanSetting.txtLoanAmountExit(Sender: TObject);
begin
  SetPayingAmount;
  SetPaymentSchedule;
end;

procedure TfmCustomerLoanSetting.txtPayingAmountChange(Sender: TObject);
begin
  SetPaymentSchedule;
end;

procedure TfmCustomerLoanSetting.txtPeriodChange(Sender: TObject);
begin
  txtInstallments.Text := Format('%d', [StrToInt(txtPeriod.Text) * StrToInt(txtDaysOfPeriod.Text)]);
end;

procedure TfmCustomerLoanSetting.SaveDetails;
begin
  if DBOp = opDenied then
  begin
    ShowMessage('It''s not active loan. Can''t be modified!');
    Exit;
  end;
  with qrySQL do
  begin
    Active := False;
    SQL.Clear;
    Params.Clear;
    case DBOp of
      opInsert: begin
        SQL.Add('Insert Into customerloansettings (CUSTOMER_CODE, LOAN_AMOUNT, ' +
                'INTEREST, PERIOD, PERIOD_TYPE, DAYS_OF_PERIOD, PAYING_AMOUNT, ' +
                'INSTALLMENTS, PAYMENT_PER_INSTALLMENT, LAST_PERIOD_PAYMENT, ' +
                'CREATEDATE) Values (:CustomerCode, :LoanAmount, :Interest, ' +
                ':Period, :PeriodType, :DaysOfPeriod, :PayingAmount, :Installments, ' +
                ':PaymentPerInstallment, :LastPeriodPayment, CURRENT_TIMESTAMP);');
      end;
      opUpdate: begin
        SQL.Add('Update customerloansettings Set LOAN_AMOUNT=:LoanAmount, ' +
                'INTEREST=:Interest, ' +
                'PERIOD=:Period, ' +
                'PERIOD_TYPE=:PeriodType, ' +
                'DAYS_OF_PERIOD=:DaysOfPeriod, ' +
                'PAYING_AMOUNT=:PayingAmount, ' +
                'INSTALLMENTS=:Installments, ' +
                'PAYMENT_PER_INSTALLMENT=:PaymentPerInstallment, ' +
                'LAST_PERIOD_PAYMENT=:LastPeriodPayment ' +
                'Where ID=:ID;');
        Params.ParamByName('ID').AsInteger := adtLoanSettings.FieldByName('ID').AsInteger;
      end;
    end;
    if Assigned(Params.FindParam('CustomerCode')) then
      Params.ParamByName('CustomerCode').Value := dblcbCustomerCode.KeyValue;
    Params.ParamByName('LoanAmount').AsString := txtLoanAmount.Text;
    Params.ParamByName('Interest').AsString := txtInterest.Text;
    Params.ParamByName('Period').AsString := txtPeriod.Text;
    Params.ParamByName('PeriodType').AsString := cbPeriodType.Items[cbPeriodType.ItemIndex];
    Params.ParamByName('DaysOfPeriod').AsString := txtDaysOfPeriod.Text;
    Params.ParamByName('PayingAmount').AsString := txtPayingAmount.Text;
    Params.ParamByName('Installments').AsString := txtInstallments.Text;
    Params.ParamByName('PaymentPerInstallment').AsString := txtPaymentPerInstallment.Text;
    Params.ParamByName('LastPeriodPayment').AsString := txtLastPeriodPayment.Text;
    try
      if not Prepared then
        Prepare;
//      Active := True;
      ExecSQL;
      ADConnection1.Commit;
      adtCustomers.Refresh;
      adtLoanSettings.Refresh;
      if (RowsAffected <= 0) or (RowsAffected > 1) then
        ShowMessage('Something wrong when save.');
    except
      on E : Exception do
      begin
        ShowMessage('Exception message = '+E.Message);
        ADConnection1.Rollback;
      end;
    end;
  end;
end;

procedure TfmCustomerLoanSetting.SetPayingAmount;
var
  Divider: Integer;
  Interest: Single;
begin
  Divider := 1;
  Interest := StrToFloat(txtInterest.Text);
  if Interest >= 1 then
    Divider := 100;
  txtPayingAmount.Text := Format('%.2f', [StrToFloat(txtLoanAmount.Text) * (1 + Interest / Divider)]);
end;

procedure TfmCustomerLoanSetting.SetPaymentSchedule;
var
  PayingAmount: Single;
  LastPeriodPayment: Single;
  Installments: Integer;
  PaymentPerInstallment: Single;
  Payment: Single;
begin
  PayingAmount := StrToFloat(txtPayingAmount.Text);
  Installments := StrToInt(txtInstallments.Text);
  if Installments = 0 then Installments := 1;  // avoid divide by zero

  if Frac(PayingAmount / Installments) > 0 then Dec(Installments);

  Payment := PayingAmount / Installments;
  PaymentPerInstallment := Int(Payment) * 1.0;
  LastPeriodPayment := Frac(Payment) * Installments;
  if LastPeriodPayment = 0 then LastPeriodPayment := PaymentPerInstallment;

  txtPaymentPerInstallment.Text := Format('%.2f', [PaymentPerInstallment]);
  txtLastPeriodPayment.Text := Format('%.2f', [LastPeriodPayment]);
end;

procedure TfmCustomerLoanSetting.InitPeriodType;
var
  PeriodType: string;
begin
  PeriodTypeList := TStringList.Create;

  cbPeriodType.Items.Clear;
  with qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    SQL.Add('Select * From period_type');
    if not Prepared then
      Prepare;
    Open;
    while not EOF do
    begin
      PeriodType := Format('%s=%s', [FieldByName('PERIOD').AsString,
                                     FieldByName('DAYS').AsString]);
      PeriodTypeList.Add(PeriodType);
      cbPeriodType.Items.Add(FieldByName('PERIOD').AsString);
      Next;
    end;
  end;
end;

function TfmCustomerLoanSetting.HaveLoanActive: boolean;
begin
  if dblcbCustomerCode.KeyValue = Null then
  begin
    ShowMessage('Customer Code is empty, can''t save now!');
    Result := True;
    Exit;
  end;
  with qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    SQL.Add('Select * From customerloansettings Where ACTIVE=''Y'' and CUSTOMER_CODE=:CustomerCode');
    Params.ParamByName('CustomerCode').Value := dblcbCustomerCode.KeyValue;
    if not Prepared then
      Prepare;
    Open;
    Result := RowsAffected > 0;
  end;
end;

end.
